package ym.ecrm.core.dao.systemManagement.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import ym.ecrm.core.common.util.Pager;
import ym.ecrm.core.dao.base.impl.HibernateEntityDAO;
import ym.ecrm.core.dao.systemManagement.IUserDAO;
import ym.ecrm.core.domain.systemManagement.User;
import ym.ecrm.core.dto.systemManagement.UserQueryConditionDTO;


@Repository
@SuppressWarnings("unchecked")
public class HibernateUserDAO extends HibernateEntityDAO<User> implements
		IUserDAO {

//	//@Override
	public Pager<User> findUsers(int pageNumber, final int pageSize) {
		Long totalCount = getHibernateTemplate().execute(new HibernateCallback<Long>() {

//			//@Override
			public Long doInHibernate(Session session)
					throws HibernateException, SQLException {
				List<Long> list = session.createQuery("select count(*) from User").list();
				if(list != null && !list.isEmpty()){
					return (Long) list.get(0);
				}
				return null;
			}
			
		});
		
		if(totalCount == null || totalCount < 1){
			return new Pager<User>(pageNumber, pageSize, 0l, null);
		}
		
		final int firstNumber = Pager.getStartIndex(pageNumber, pageSize);
		
		List<User> list  = getHibernateTemplate().executeFind(new HibernateCallback<List<User>>() {

			////@Override
			public List<User> doInHibernate(Session session)
					throws HibernateException, SQLException {
				return session.createQuery("from User").setFirstResult(firstNumber).setMaxResults(pageSize).list();
			}
		});
		getHibernateTemplate().flush();
		return new Pager<User>(pageNumber,pageSize,totalCount,list);
	}

	//@Override
	public Pager<User> findUsers(final UserQueryConditionDTO queryCondition,
			final int pageNumber, final int pageSize) {

		StringBuffer countbuffer = new StringBuffer();
		countbuffer.append("select count(*) from User user");
		countbuffer.append(" where (:userName is null or lower(user.userName) like :userName)");
		countbuffer.append(" and (:userCode is null or lower(user.userCode) like :userCode)");
		countbuffer.append(" and (:state is null or lower(user.state) like :state)");
		countbuffer.append(" and (:organizationName is null or lower(user.organization.name) like :organizationName)");
		
		final String countString = countbuffer.toString();

		final StringBuffer hqlbuffer = new StringBuffer();
		
		hqlbuffer.append("from User user");
		hqlbuffer.append(" where (:userName is null or lower(user.userName) like :userName)");
		hqlbuffer.append(" and (:userCode is null or lower(user.userCode) like :userCode)");
		hqlbuffer.append(" and (:state is null or lower(user.state) like :state)");
		hqlbuffer.append(" and (:organizationName is null or lower(user.organization.name) like :organizationName)");
		hqlbuffer.append(" order by user.id asc");
		final String hqlString = hqlbuffer.toString();

		Long totalCount = getHibernateTemplate().execute(
				new HibernateCallback() {

					//@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createQuery(countString);
						query.setParameter("userName", 
								queryCondition.getUserName() == null? null : "%"+queryCondition.getUserName()+"%");
						query.setParameter("userCode", 
								queryCondition.getUserCode() == null? null:"%"+queryCondition.getUserCode()+"%");
						query.setParameter("state", queryCondition.getState() == null? null:"%"+queryCondition.getState()+"%");
						query.setParameter("organizationName", 
								queryCondition.getDepartment() == null? null:"%"+queryCondition.getDepartment()+"%");
						List list = query.list();
						if(list != null && !list.isEmpty()){
							return list.get(0);
						}
						return null;
					}

				});

		if (totalCount == null || totalCount < 0) {
			return null;
		}
		
		List<User> list  = getHibernateTemplate().executeFind(new HibernateCallback<List<User>>() {

			//@Override
			public List<User> doInHibernate(Session session)
					throws HibernateException, SQLException {
				int firstIndex = Pager.getStartIndex(pageNumber, pageSize);
				Query query = session.createQuery(hqlString);
				query.setParameter("userName", 
						queryCondition.getUserName() == null? null :"%"+queryCondition.getUserName()+"%");
				query.setParameter("userCode", 
						queryCondition.getUserCode() == null? null:"%"+queryCondition.getUserCode()+"%");
				query.setParameter("state", queryCondition.getState() == null? null:"%"+queryCondition.getState()+"%");
				query.setParameter("organizationName", 
						queryCondition.getDepartment() == null? null:"%"+queryCondition.getDepartment()+"%");
				if(pageSize == 0){
					return query.list();
				}else{
					return query.setFirstResult(firstIndex).setMaxResults(pageSize).list();
				}
				
			}
		});
		
		return new Pager<User>(pageNumber,pageSize,totalCount,list);
		
	}

	//@Override
	public List<User> findUsers(UserQueryConditionDTO queryCondition) {
		
		return findUsers(queryCondition,0,0).getData();
	}

	//@Override
	public Pager<User> findUsersByOrganizationId(final long organizationId, 
			final int pageNumber, final int pageSize) {
		final String countHql = "select count(*) from User u where u.organization.id = :organizationId";
		final String hql = "from User u where u.organization.id = :organizationId";
		long totalCount = getHibernateTemplate().execute(
				new HibernateCallback<Long>() {

			//@Override
			public Long doInHibernate(Session session) throws HibernateException,
					SQLException {
				Query query = session.createQuery(countHql);
				query.setParameter("organizationId", organizationId);
				List<Long> list = query.list();
				if(list != null && list.size() != 0){
					return (Long) list.get(0);
				}
				return new Long(0);
			}
		});
		
		if(totalCount <= 0){
			return null;
		}
		
		List<User> list = getHibernateTemplate().executeFind(
				new HibernateCallback<List<User>>() {

					//@Override
					public List<User> doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createQuery(hql);
						query.setParameter("organizationId", organizationId);
						return query.setFirstResult(
								Pager.getStartIndex(pageNumber, pageSize))
								.setMaxResults(pageSize).list();
					}
		});
		return new Pager<User>(pageNumber, pageSize, totalCount, list);
	}
}
